

import os
import xlwings as xw

SRC_FILE = 'cc.xlsx'

DST_FOLDER = './dst'

AREA_DICT = {
    '玉溪市': '530400',
    '市辖区': '530401',
    '红塔区': '530402',
    '江川县': '530421',
    '澄江县': '530422',
    '通海县': '530423',
    '华宁县': '530424',
    '易门县': '530425',
    '峨山县': '530426',
    '新平县': '530427',
    '元江县': '530428',
}


def read_all_xlsx_lines():
    '''将文档全部行读取进来'''
    wb = xw.Book(SRC_FILE)
    sht = wb.sheets['Sheet1']

    info = sht.used_range

    nrows = info.last_cell.row
    ncols = info.last_cell.column

    lines = sht.range((1, 1), (nrows, ncols)).value
    return lines


def find_matched_lines(lines, keyword, fn):
    '''找到匹配的行'''
    result = []
    to_pop_list = []

    for i, line in enumerate(lines):
        mat = fn(line[2], keyword)
        if mat:
            line[2] = "'" + line[2]
            result.append(line)
            to_pop_list.insert(0, i)

    for x in to_pop_list:
        lines.pop(x)

    return result


def check_area_code(credit_code, area_code):
    if credit_code != None:
        return credit_code[2:8] == area_code
    else:
        return False


def ergodic_area(lines):
    for x in AREA_DICT:
        generate_one_area(lines, x, AREA_DICT[x])


def generate_one_area(lines, name, code):

    print('Parsing...', name)

    wb = xw.Book()
    sht = wb.sheets[0]
    head = ['序号', '受评主体名称', '统一社会信用代码', '综合得分', '评价等级']
    sht.range((1, 1), (1, 5)).value = head
    sht.range((1, 2)).column_width = 40
    sht.range((1, 3)).column_width = 20

    matched_lines = find_matched_lines(lines, code, check_area_code)

    start = 2

    rows = len(matched_lines) + 2
    sht.range((2, 1), (rows, 5)).value = matched_lines

    wb.save(DST_FOLDER + '/' + name + '（' +
            str(len(matched_lines)) + '家）' + '.xlsx')
    wb.close()

    print('Done', name)


def main():

    try:
        app = xw.App(visible=False)

        if not os.path.exists(DST_FOLDER):
            os.mkdir(DST_FOLDER)

        print('Waiting...')

        lines = read_all_xlsx_lines()

        ergodic_area(lines)
        # get_one_area(lines, '元江县', '530428')
    
    finally:
        app.quit()


if __name__ == '__main__':
    main()
